﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Gx.Model.Role;
using Gx.Common;
using Dapper;


namespace Gx.Dal.Role
{
    /// <summary>
    /// 页面权限
    /// </summary>
    public class M_PageUrlInfoDal : SqlServerHelper
    {
        /// <summary>
        /// 根据角色获取所有权限
        /// </summary>
        /// <param name="RoleID"></param>
        /// <returns></returns>
        public List<M_PageUrlInfo> GetMenuInfoByRoleId(int RoleID)
        {

            string strSql = @"
SELECT 
		DISTINCT
        Sort ,
        UrlName ,
        Url ,
        Icon ,
        LevelID ,
        Status ,
        dbo.M_PageUrlInfo.ID
FROM    dbo.M_PageUrlInfo WITH ( NOLOCK )
        INNER JOIN dbo.M_RolePageUrlInfo WITH ( NOLOCK ) ON dbo.M_RolePageUrlInfo.PageUrlID = dbo.M_PageUrlInfo.ID
WHERE   [Status] = 0
        AND RoleID = @RoleID
ORDER BY dbo.M_PageUrlInfo.Sort ASC
";
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                List<M_PageUrlInfo> list = conn.Query<M_PageUrlInfo>(strSql.ToString(), new { RoleID }).ToList();
                return list;
            }
        }
        /// <summary>
        /// 根据角色获取所有权限
        /// </summary>
        /// <param name="roleId"></param>
        /// <returns></returns>
        public List<M_PageUrlInfo> GetMenuInfo(int RoleID)
        {

            string strSql = @"
SELECT 
DISTINCT
        Sort ,
        UrlName ,
        Url ,
        Icon ,
        LevelID ,
        Status ,
        dbo.M_PageUrlInfo.ID
FROM    dbo.M_PageUrlInfo WITH ( NOLOCK )
        INNER JOIN dbo.M_RolePageUrlInfo WITH ( NOLOCK ) ON dbo.M_RolePageUrlInfo.PageUrlID = dbo.M_PageUrlInfo.ID
WHERE   [Status] = 0
        AND RoleID =@RoleID
ORDER BY dbo.M_PageUrlInfo.Sort ASC
";
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                List<M_PageUrlInfo> list = conn.Query<M_PageUrlInfo>(strSql, new { RoleID }).ToList();
                return list;
            }
        }

        /// <summary>
        /// 获取页面路径列表
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public List<M_PageUrlInfo> GetPageUrlInfoList(string strWhere)
        {
            string strSql = $@"
SELECT  ID ,
        UrlName ,
        Url ,
        Icon ,
        LevelID ,
        Status ,
        Sort
FROM    M_PageUrlInfo WITH ( NOLOCK )
WHERE   Status = 0 {strWhere}
ORDER BY Sort ASC";
        
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                List<M_PageUrlInfo> list = conn.Query<M_PageUrlInfo>(strSql).ToList();
                return list;
            }
        }


        public int AddM_PageUrlInfo(M_PageUrlInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO M_PageUrlInfo");
            strSql.Append("(");
            strSql.Append("UrlName,");
            strSql.Append("Url,");
            strSql.Append("Icon,");
            strSql.Append("LevelID,");
            strSql.Append("Status,");
            strSql.Append("Sort");
            strSql.Append(")");
            strSql.Append("VALUES");
            strSql.Append("(");
            strSql.Append("@UrlName,");
            strSql.Append("@Url,");
            strSql.Append("@Icon,");
            strSql.Append("@LevelID,");
            strSql.Append("@Status,");
            strSql.Append("@Sort");
            strSql.Append("); ");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                return conn.Execute(strSql.ToString(), model);
            }
        }


        public int DelM_PageUrlInfo(string ID)
        {
            string strSql = "DELETE FROM M_PageUrlInfo WHERE ID=@ID";
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                return conn.Execute(strSql, new { ID });
            }
        }


        public int EditM_PageUrlInfo(M_PageUrlInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE M_PageUrlInfo SET ");
            strSql.Append(" UrlName=@UrlName,");
            strSql.Append(" Url=@Url,");
            strSql.Append(" Icon=@Icon,");
            strSql.Append(" LevelID=@LevelID,");
            strSql.Append(" Status=@Status,");
            strSql.Append(" Sort=@Sort");
            strSql.Append(" WHERE ID=@ID");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                return conn.Execute(strSql.ToString(), model);
            }
        }

        /// <summary>
        /// 获取页面实体
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public M_PageUrlInfo GetM_PageUrlInfoModel(string ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("ID");
            strSql.Append(",UrlName");
            strSql.Append(",Url");
            strSql.Append(",Icon");
            strSql.Append(",LevelID");
            strSql.Append(",Status");
            strSql.Append(",Sort");
            strSql.Append(" FROM M_PageUrlInfo WITH(NOLOCK) WHERE ID=@ID");
            using (SqlConnection conn = ConfigHelper.Open(ConfigHelper.ConnectionStrings))
            {
                M_PageUrlInfo model = conn.Query<M_PageUrlInfo>(strSql.ToString(), new { ID }).SingleOrDefault();
                return model;
            }
        }
    }
}
